set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


insert overwrite table jms_dm.dm_whole_effect_zhitu_outport_dt partition(dt)
select
      sign_date                   as sign_date                 --签收日期
     ,send_network_code           as start_network_code        --始发网点编码
     ,send_network_name           as start_network_name        --始发网点名称
     ,send_fran_code              as start_fran_code           --始发加盟商code
     ,send_fran_name              as start_fran_name           --始发加盟商名称
     ,send_agent_code             as start_agent_code          --始发代理区code
     ,send_agent_name             as start_agent_name          --始发代理区名称
     ,send_regional_id            as start_regional_code       --始发大区编码
     ,send_regional_desc          as start_regional_name       --始发大区名称
     ,send_area_id                as start_area_code           --始发区县编码
     ,send_area_desc              as start_area_name           --始发区县名称
     ,send_city_id                as start_city_code           --始发城市编码
     ,send_city_desc              as start_city_name           --始发城市名称
     ,send_provider_id            as start_provider_code       --始发省份编码
     ,send_provider_desc          as start_provider_name       --始发省份名称 
     ,sign_provider_id            as end_provider_code         --签收省份编码
     ,sign_provider_desc          as end_provider_name         --签收省份名称 
     ,round(sum(custorder_taking_difftime   ),2) as custorder_taking_time  
     ,round(sum(custorder_pre_sign_difftime ),2) as custorder_pre_sign_time  
     ,round(sum(pre_sign_taking_difftime    ),2) as taking_pre_sign_time  
     ,sum(case when custorder_taking_difftime   >0 then 1 else 0 end) as custorder_taking_cnt
     ,sum(case when custorder_pre_sign_difftime >0 then 1 else 0 end) as custorder_pre_sign_cnt
     ,sum(case when pre_sign_taking_difftime    >0 then 1 else 0 end) as taking_pre_sign_cnt
     ,nvl(send_virt_code,send_agent_code) as start_virt_code          --始发虚拟代理区编码
     ,nvl(send_virt_name,send_agent_name) as start_virt_name          --始发虚拟代理区名称
     ,wide.sign_date as dt --签收日期
from jms_dws.dws_wide_unsign_whole_effect_dt wide
where wide.dt between date_sub('{{ execution_date | cst_ds }}',30) and '{{ execution_date | cst_ds }}'
  and send_network_code is not null
  and sign_date between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
  and if_reback    <> 1
  and if_end_piece <> 1
  and if_intercept <> 1
group by sign_date           
        ,send_network_code   
        ,send_network_name   
        ,send_fran_code      
        ,send_fran_name      
        ,send_agent_code     
        ,send_agent_name     
        ,send_regional_id    
        ,send_regional_desc  
        ,send_area_id        
        ,send_area_desc      
        ,send_city_id        
        ,send_city_desc      
        ,send_provider_id    
        ,send_provider_desc  
        ,sign_provider_id    
        ,sign_provider_desc  
        ,nvl(send_virt_code,send_agent_code) 
        ,nvl(send_virt_name,send_agent_name) 
distribute by sign_date,pmod(hash(rand()),10)
;